#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   curd.py
@Time    :   2021/02/08 00:12:14
@Author  :   superjin
@Version :   1.0
'''

from hashlib import new
from os import name
from typing import Any, Optional, List
from sqlalchemy import func
from sqlalchemy.orm import Session
from core.schemas.reservation import ReservationCreate, ReservationDelete, ReservationUpdate
from models.reservation import Reservation
from models.participant import Participant
from models.classroom import Classroom
from models.user import User


class CRUDReservation:
    def remove(self,db: Session,reservation_id:int ):
        db.query(Reservation).filter_by(reservation_id=reservation_id).delete(synchronize_session=False)
        db.commit()
        return {}
    
    def create(self, db: Session, reservation: ReservationCreate,user_id:int) :
        reservation = Reservation(reservation.classroomId, reservation.reservationType, 
                        user_id,reservation.get_times_as_datetimes()[0],
                        reservation.get_times_as_datetimes()[1],reservation.description)
        db.add(reservation)
        db.commit()
        return reservation
    
    def create_c(self, db: Session, classroomId,reservationType,user_id,start_time,end_time,description,course_id) :
        reservation = Reservation(classroomId,reservationType, 
                        user_id,start_time,
                        end_time,description,course_id)
        db.add(reservation)
        db.commit()
        return reservation

    def update_reservation(self, db: Session, reservation_id, new_data: ReservationUpdate):
        update_data = new_data.dict(exclude_unset=True)
        res = db.query(Reservation).filter_by(reservation_id=reservation_id).update(update_data)
        db.commit()
        return res

    def list_participation_by_id(self,db:Session,page:int ,page_size:int,reservation_id):
        res = db.query(Participant.check_in,
                        Participant.participant_id,
                        Participant.status,
                        Participant.check_out, 
                        Reservation.reservation_id,
                        Reservation.start_time,
                        Reservation.end_time,
                        User.user_id,
                        User.username,
                        User.name.label('user_name'),
                        func.concat(Classroom.building_name,'-',Classroom.room_number).label('location'),
                        Reservation.reservation_type).filter_by(reservation_id=reservation_id).join(Reservation, 
                    Participant.reservation_id == Reservation.reservation_id).join(User, 
                    Participant.user_id == User.user_id).join(Classroom,
                    Reservation.classroom_id == Classroom.classroom_id).offset((page-1)*page_size).limit(page_size).all()
        total = db.query(func.count(Participant.participant_id)).filter_by(reservation_id=reservation_id).scalar()
        return res,total


    def list_reservations(self,db:Session,page:int ,page_size:int,creator_id:int = 0):
        if creator_id !=0:
            res = db.query(
                Reservation.reservation_id,
                Reservation.reservation_type,
                Reservation.start_time,
                Reservation.end_time,
                Reservation.creator_id,
                Reservation.status,
                Reservation.description,
                func.concat(Classroom.building_name,'-',Classroom.room_number).label('location'),
                User.name.label('creater_name'),
                func.count(Participant.participant_id).label('participant_count')
            ).filter_by(creator_id=creator_id) \
             .filter(Reservation.reservation_type != 'class') \
             .join(User, User.user_id == Reservation.creator_id) \
             .join(Classroom, Reservation.classroom_id == Classroom.classroom_id) \
             .join(Participant, Participant.reservation_id == Reservation.reservation_id, isouter=True) \
             .group_by(
                Reservation.reservation_id,
                Reservation.reservation_type,
                Reservation.start_time,
                Reservation.end_time,
                Reservation.creator_id,
                Reservation.status,
                Reservation.description,
                Classroom.room_number,
                Classroom.building_name,
                User.name
             ) \
             .offset((page - 1) * page_size) \
             .limit(page_size) \
             .all()

        # 查询总记录数
            total = db.query(func.count(Reservation.reservation_id)).filter(Reservation.reservation_type != 'class').scalar()

            return res, total
            
        res = db.query(
                Reservation.reservation_id,
                Reservation.reservation_type,
                Reservation.start_time,
                Reservation.end_time,
                Reservation.creator_id,
                Reservation.status,
                Reservation.description,
                func.concat(Classroom.building_name,'-',Classroom.room_number).label('location'),
                User.name.label('creater_name'),
                func.count(Participant.participant_id).label('participant_count')
            ).filter(Reservation.reservation_type != 'class') \
             .join(User, User.user_id == Reservation.creator_id) \
             .join(Classroom, Reservation.classroom_id == Classroom.classroom_id) \
             .join(Participant, Participant.reservation_id == Reservation.reservation_id, isouter=True) \
             .group_by(
                Reservation.reservation_id,
                Reservation.reservation_type,
                Reservation.start_time,
                Reservation.end_time,
                Reservation.creator_id,
                Reservation.status,
                Reservation.description,
                Classroom.room_number,
                Classroom.building_name,
                User.name
             ) \
             .offset((page - 1) * page_size) \
             .limit(page_size) \
             .all()

        # 查询总记录数
        total = db.query(func.count(Reservation.reservation_id)).filter(Reservation.reservation_type != 'class') .scalar()

        return res, total


reservation = CRUDReservation()
